package com.example.recuitactivity.helper;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import androidx.annotation.Nullable;

import com.example.recuitactivity.bean.Admin;
import com.example.recuitactivity.bean.Browse;
import com.example.recuitactivity.bean.Candidate;
import com.example.recuitactivity.bean.Collection;
import com.example.recuitactivity.bean.Deliver;
import com.example.recuitactivity.bean.Enterprise;
import com.example.recuitactivity.bean.Interview;
import com.example.recuitactivity.bean.RecuitInfo;
import com.example.recuitactivity.bean.Resume;

import java.util.ArrayList;
import java.util.List;

public class DbOpenHelper extends SQLiteOpenHelper {
    private static final int VERSION = 1;
    private static final String DBNAME = "Recuit.db";   //  创建数据库名叫 Recuit
    private Context mContext;

    public DbOpenHelper(Context context) {
        super(context, DBNAME, null, VERSION);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //建管理员表
        db.execSQL("create table if not exists admins" +
                " (_aid integer primary key autoincrement ," +
                "ausername varchar(20)," +
                "apassword varchar(20)," +
                "astatus varchar(20))");
        //建求职者表
        db.execSQL("create table if not exists candidates" +
                "(_cid integer primary key autoincrement," +
                "cusername varchar(20) unique," +
                "cpassword varchar(20)," +
                "ctelephone varchar(20)," +
                "csex varchar(20) check (csex in('男','女'))," +
                "cstatus varchar(20),"+
                "cupdate_flag integer)");
        //建企业表
        db.execSQL("create table if not exists enterprises" +
                "(_eid integer primary key autoincrement," +
                "eusername varchar(20) unique," +
                "epassword varchar(20)," +
                "ecompany varchar(20) unique," +
                "escale varchar(20)," +
                "estatus varchar(20))");
        //建简历表
        db.execSQL("create table if not exists resumes" +
                "(_rid integer primary key autoincrement," +
                "rcandidate_id integer," +
                "rname varchar(20)," +
                "rsex varchar(20) check (rsex in('男','女',''))," +
                "rschool varchar(20)," +
                "rage integer," +
                "rpexp varchar(100)," +
                "rrexp varchar(100)," +
                "rtele varchar(20)," +
                "redubg varchar(20) check (redubg in('高中','大专','本科','硕士','博士',''))," +
                "FOREIGN KEY(rcandidate_id) REFERENCES candidates(_cid))");
        //建岗位信息表
        db.execSQL("create table if not exists posts" +
                "(_pid integer primary key autoincrement," +
                "pname varchar(20)," +
                "pepname varchar(20)," +
                "psalary varchar(20)," +
                "pedurequire varchar(20)," +
                "plocation varchar(20)," +
                "pdetail varchar(100)," +
                "pscale varchar(20),"+
                "pupdate_time varchar(20))");
        //建投递信息表
        db.execSQL("create table if not exists delivers" +
                "(_dcid integer," +
                "_deid integer," +
                "_dpid integer," +
                "dpname varchar(20)," +
                "dtime varchar(20)," +
                "dstate varchar(20)," +
                "constraint pk1 primary key(_dcid,_deid,_dpid)," +
                "FOREIGN KEY(_dcid) REFERENCES candidates(_cid)," +
                "FOREIGN KEY(_deid) REFERENCES enterprises(_eid)," +
                "FOREIGN KEY(_dpid) REFERENCES posts(_pid))");
        //建面试通知信息表
        db.execSQL("create table if not exists interviews" +
                "(_iid integer primary key autoincrement," +
                "icid integer," +
                "icandidate varchar(20)," +
                "ipost varchar(20)," +
                "ienterprise varchar(20)," +
                "idate varchar(20)," +
                "itips varchar(200)," +
                "isend_time varchar(20)," +
                "FOREIGN KEY(icid) REFERENCES candidates(_cid))");

        //建立收藏表
        db.execSQL("create table if not exists collects"+
                "(_cocid integer,"+
                "_copid integer,"+
                "constraint pk1 primary key(_cocid,_copid),"+
                "FOREIGN KEY(_cocid) REFERENCES candidates(_cid)," +
                "FOREIGN KEY(_copid) REFERENCES posts(_pid))");

        //建立浏览岗位表
        db.execSQL("create table if not exists browses"+
                "(_bcid integer,"+
                "_bpid integer,"+
                "bcount integer,"+
                "blast_time varchar(20),"+
                "constraint pk1 primary key(_bcid,_bpid),"+
                "FOREIGN KEY(_bcid) REFERENCES candidates(_cid),"+
                "FOREIGN KEY(_bpid) REFERENCES posts(_pid))");

        //插入固定管理员账户
        db.execSQL("insert into admins (ausername,apassword,astatus) values('ymh','123456','admin')");
        db.execSQL("insert into admins (ausername,apassword,astatus) values('admin','123456','admin')");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists admins");
        db.execSQL("drop table if exists candidates");
        db.execSQL("drop table if exists enterprises");
        db.execSQL("drop table if exists resumes");
        db.execSQL("drop table if exists posts");
        db.execSQL("drop table if exists delivers");
        db.execSQL("drop table if exists interviews");
        db.execSQL("drop table if exists collects");
        db.execSQL("drop table if exists browses");
        onCreate(db);
    }

    //得到admins表的所有信息
    public List<List<String>> getAllAdmins(){
        SQLiteDatabase db = getReadableDatabase();
        List<List<String>> ans=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from admins ",null);
        if(cursor.moveToFirst()){
            do{
                int index_id=cursor.getColumnIndex("_aid");
                int index_username=cursor.getColumnIndex("ausername");
                int index_password=cursor.getColumnIndex("apassword");
                int index_status=cursor.getColumnIndex("astatus");

                int id=cursor.getInt(index_id);
                String username=cursor.getString(index_username);
                String password=cursor.getString(index_password);
                String status=cursor.getString(index_status);

                List<String> list=new ArrayList<>();
                list.add(String.valueOf(id));
                list.add(username);
                list.add(password);
                list.add(status);
                ans.add(list);
            }while (cursor.moveToNext());
        }
        return ans;
    }

    //插入求职者,同时向简历表中插入数据，但是为空
    public void insertCandidate(Candidate candidate) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into candidates (cusername,cpassword,ctelephone,csex,cstatus,cupdate_flag) values(?,?,?,?,?,?)",
                new Object[]{candidate.getUsername(), candidate.getPassword(), candidate.getTelephone(), candidate.getSex(), candidate.getStatus(),candidate.getUpdateFlag()});
        //插入空简历
        long rcandidate_id = getCandidateIdByUsername(candidate.getUsername());
        db.execSQL("insert into resumes (rname,rsex,rschool,rage,rpexp,rrexp,rtele,redubg,rcandidate_id) values('','','','','','','','',?)", new Object[]{rcandidate_id});
    }

    //根据求职者id删除该求职者
    public void deleteCandidateById(long cid){
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("delete from resumes where rcandidate_id=?",new Object[]{cid});//删除该求职者简历信息
        db.execSQL("delete from candidates where _cid=?",new Object[]{cid});//删除该求职者信息
    }

    //在candidates表中根据usraname得到id值
    public long getCandidateIdByUsername(String username) {
        SQLiteDatabase db = getWritableDatabase();
        long candidates_id=0;
        Cursor cursor;
        cursor = db.rawQuery("select _cid from candidates where cusername=?", new String[]{username});
        if(cursor.moveToFirst()){
            int index_id = cursor.getColumnIndex("_cid");
            candidates_id = cursor.getLong(index_id);
        }
        cursor.close();
        return candidates_id;
    }

    public Candidate getCandidateByCId(long cid){
        SQLiteDatabase db = getReadableDatabase();
        Candidate candidate=null;
        Cursor cursor = db.rawQuery("select _cid,cusername,cpassword,ctelephone,csex,cstatus,cupdate_flag from candidates where _cid=?", new String[]{String.valueOf(cid)});
        if(cursor.moveToFirst()){
            int cid_index=cursor.getColumnIndex("_cid");
            int username_index=cursor.getColumnIndex("cusername");
            int password_index=cursor.getColumnIndex("cpassword");
            int telephone_index=cursor.getColumnIndex("ctelephone");
            int sex_index=cursor.getColumnIndex("csex");
            int status_index=cursor.getColumnIndex("cstatus");
            int update_flag_index=cursor.getColumnIndex("cupdate_flag");

            int id=cursor.getInt(cid_index);
            String username=cursor.getString(username_index);
            String password=cursor.getString(password_index);
            String telephone=cursor.getString(telephone_index);
            String sex=cursor.getString(sex_index);
            String status=cursor.getString(status_index);
            int update_flag=cursor.getInt(update_flag_index);
            candidate=new Candidate(id,username,password,telephone,sex,status,update_flag);
        }
        cursor.close();
        return candidate;
    }

    //在candidate表中根据id得到性别
    public String getCandidateSexById(long candidate_id){
        SQLiteDatabase db=getReadableDatabase();
        String sex="";
        Cursor cursor=db.rawQuery("select csex from candidates where _cid=?",new String[]{String.valueOf(candidate_id)});
        if(cursor.moveToFirst()){
            int index_sex = cursor.getColumnIndex("csex");
            sex = cursor.getString(index_sex);
        }
        return sex;
    }

    //在candidate表中根据id得到cupdate_flag
    public int getCandidateFlagById(long candidate_id){
        SQLiteDatabase db=getReadableDatabase();
        Cursor cursor=db.rawQuery("select cupdate_flag from candidates where _cid=?",new String[]{String.valueOf(candidate_id)});
        int flag=0;
        if(cursor.moveToFirst()){
            int index_sex = cursor.getColumnIndex("cupdate_flag");
            flag = cursor.getInt(index_sex);
        }
        return flag;
    }

    //在candidates表中更新cupdate_flag
    public void updateCandidateFlagByCid(long cid,int flag){
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL("update candidates set cupdate_flag=? where _cid=?", new Object[]{flag,cid});
    }

    //判断是否已存在某个求职者的用户名
    public boolean isExistCandidate(String username) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from candidates where cusername=?",new String[]{username});
        boolean flag = cursor.moveToFirst();
        cursor.close();
        return flag;
    }

    //得到candidates表的所有信息
    public List<List<String>> getAllCandidates(){
        SQLiteDatabase db = getReadableDatabase();
        List<List<String>> ans=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from candidates ",null);
        if(cursor.moveToFirst()){
            do{
                int index_id=cursor.getColumnIndex("_cid");
                int index_username=cursor.getColumnIndex("cusername");
                int index_password=cursor.getColumnIndex("cpassword");
                int index_tele=cursor.getColumnIndex("ctelephone");
                int index_sex=cursor.getColumnIndex("csex");
                int index_status=cursor.getColumnIndex("cstatus");
                int index_update_flag=cursor.getColumnIndex("cupdate_flag");

                long id=cursor.getLong(index_id);
                String username=cursor.getString(index_username);
                String password=cursor.getString(index_password);
                String tele=cursor.getString(index_tele);
                String sex=cursor.getString(index_sex);
                String status=cursor.getString(index_status);
                int updateFlag=cursor.getInt(index_update_flag);


                List<String> list=new ArrayList<>();
                list.add(String.valueOf(id));
                list.add(username);
                list.add(password);
                list.add(tele);
                list.add(sex);
                list.add(status);
                list.add(String.valueOf(updateFlag));
                ans.add(list);
            }while (cursor.moveToNext());
        }
        return ans;
    }

    //插入企业
    public void insertEnterprise(Enterprise enterprise) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into enterprises (eusername,epassword,ecompany,escale,estatus) values(?,?,?,?,?)",
                new Object[]{enterprise.getUsername(), enterprise.getPassword(), enterprise.getCompany(), enterprise.getScale(), enterprise.getStatus()});
    }

    //根据企业id删除该用户
    public void deleteEnterpriseById(long eid){
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("delete from posts where pepname=?",new Object[]{getEnterpriseCompanyById(eid)});//删除该企业发布的所有岗位信息
        db.execSQL("delete from enterprises where _eid=?",new Object[]{eid});//删除该企业的信息
    }

    //根据用户名判断是否已存在某个企业,保证企业用户名的唯一性
    public boolean isExistEnterpriseByusername(String username) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from enterprises where eusername=?",  new String[]{username});
        boolean flag = cursor.moveToFirst();
        cursor.close();
        return flag;
    }

    //根据企业名判断是否已存在某个企业，保证企业名的唯一性
    public boolean isExistEnterpriseBycompany(String company) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from enterprises where ecompany=?", new String[]{company});
        boolean flag = cursor.moveToFirst();
        cursor.close();
        return flag;
    }

    //在enterprises表中根据usraname得到企业名值
    public String getEnterpriseCompanyByUsername(String username) {
        SQLiteDatabase db = getReadableDatabase();
        String comany="";
        Cursor cursor;
        cursor = db.rawQuery("select ecompany from enterprises where eusername=?",  new String[]{username});
        if(cursor.moveToFirst()){
            int index_company = cursor.getColumnIndex("ecompany");
            comany = cursor.getString(index_company);
        }
        cursor.close();
        return comany;
    }

    //根据company值得到企业id
    public long getEnterpriseIdByCompany(String company) {
        SQLiteDatabase db = getWritableDatabase();
        long enterprise_id=0;
        Cursor cursor;
        cursor = db.rawQuery("select _eid from enterprises where ecompany=?",new String[]{company});
        if(cursor.moveToFirst()){
            int index_id = cursor.getColumnIndex("_eid");
            enterprise_id = cursor.getLong(index_id);
        }
        cursor.close();
        return enterprise_id;
    }

    //根据企业id得到企业名
    public String getEnterpriseCompanyById(long enterprise_id){
        SQLiteDatabase db = getReadableDatabase();
        String comany="";
        Cursor cursor;
        cursor = db.rawQuery("select ecompany from enterprises where _eid=?",new String[]{String.valueOf(enterprise_id)});
        if(cursor.moveToFirst()){
            int index_company = cursor.getColumnIndex("ecompany");
            comany = cursor.getString(index_company);
        }
        cursor.close();
        return comany;
    }

    //得到enterprises表的所有信息
    public List<List<String>> getAllEnterprises(){
        SQLiteDatabase db = getReadableDatabase();
        List<List<String>> ans=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from enterprises ",null);
        if(cursor.moveToFirst()){
            do{
                int index_id=cursor.getColumnIndex("_eid");
                int index_username=cursor.getColumnIndex("eusername");
                int index_password=cursor.getColumnIndex("epassword");
                int index_comapny=cursor.getColumnIndex("ecompany");
                int index_scale=cursor.getColumnIndex("escale");
                int index_status=cursor.getColumnIndex("estatus");

                long id=cursor.getLong(index_id);
                String username=cursor.getString(index_username);
                String password=cursor.getString(index_password);
                String company=cursor.getString(index_comapny);
                String scale=cursor.getString(index_scale);
                String status=cursor.getString(index_status);


                List<String> list=new ArrayList<>();
                list.add(String.valueOf(id));
                list.add(username);
                list.add(password);
                list.add(company);
                list.add(scale);
                list.add(status);
                ans.add(list);
            }while (cursor.moveToNext());
        }
        return ans;
    }

    //根据求职者的id得到对应简历的所有信息
    public Resume getResumeInfoById(Long candidateId) {
        SQLiteDatabase db = getReadableDatabase();
        Resume resume=null;
        Cursor cursor = db.rawQuery("select _rid,rname,rsex,rschool,rage,rpexp,rrexp,rtele,redubg from resumes where rcandidate_id=?", new String[]{String.valueOf(candidateId)});
        if(cursor.moveToFirst()){
            int id_index=cursor.getColumnIndex("_rid");
            int name_index=cursor.getColumnIndex("rname");
            int sex_index=cursor.getColumnIndex("rsex");
            int school_index=cursor.getColumnIndex("rschool");
            int age_index=cursor.getColumnIndex("rage");
            int project_exp_index=cursor.getColumnIndex("rpexp");
            int reward_exp_index=cursor.getColumnIndex("rrexp");
            int tele_index=cursor.getColumnIndex("rtele");
            int edubg_index=cursor.getColumnIndex("redubg");

            Long id=cursor.getLong(id_index);
            String name=cursor.getString(name_index);
            String sex=cursor.getString(sex_index);
            String school=cursor.getString(school_index);
            int age=cursor.getInt(age_index);
            String project_exp=cursor.getString(project_exp_index);
            String reward_exp=cursor.getString(reward_exp_index);
            String tele=cursor.getString(tele_index);
            String edubg=cursor.getString(edubg_index);
            resume=new Resume(id,candidateId,name,sex,school,age,project_exp,reward_exp,tele,edubg);
        }
        cursor.close();
        return resume;
    }

    //更新简历信息
    public void updateResume(Resume resume){
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL("update resumes set rname=?,rsex=?,rschool=?,rage=?,rpexp=?,rrexp=?,rtele=?,redubg=? where _rid=?",
                new Object[]{resume.getName(),resume.getSex(),resume.getSchool(),resume.getAge(),resume.getProject_exp(),
                             resume.getReward_exp(),resume.getTelephone(),resume.getEdu_background(),resume.getId()});
    }

    //得到resumes表的所有信息
    public List<List<String>> getAllResumes(){
        SQLiteDatabase db = getReadableDatabase();
        List<List<String>> ans=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from resumes ",null);
        if(cursor.moveToFirst()){
            do{
                int index_id=cursor.getColumnIndex("_rid");
                int index_candidate_id=cursor.getColumnIndex("rcandidate_id");
                int index_name=cursor.getColumnIndex("rname");
                int index_sex=cursor.getColumnIndex("rsex");
                int index_school=cursor.getColumnIndex("rschool");
                int index_age=cursor.getColumnIndex("rage");
                int index_pexp=cursor.getColumnIndex("rpexp");
                int index_rexp=cursor.getColumnIndex("rrexp");
                int index_tele=cursor.getColumnIndex("rtele");
                int index_edubg=cursor.getColumnIndex("redubg");

                long id=cursor.getLong(index_id);
                long candidate_id=cursor.getLong(index_candidate_id);
                String name=cursor.getString(index_name);
                String sex=cursor.getString(index_sex);
                String school=cursor.getString(index_school);
                String age=cursor.getString(index_age);
                String pexp=cursor.getString(index_pexp);
                String rexp=cursor.getString(index_rexp);
                String tele=cursor.getString(index_tele);
                String edubg=cursor.getString(index_edubg);


                List<String> list=new ArrayList<>();
                list.add(String.valueOf(id));
                list.add(String.valueOf(candidate_id));
                list.add(name);
                list.add(sex);
                list.add(school);
                list.add(age);
                list.add(pexp);
                list.add(rexp);
                list.add(tele);
                list.add(edubg);
                ans.add(list);
            }while (cursor.moveToNext());
        }
        return ans;
    }

    //插入岗位信息
    public void insertPost(RecuitInfo recuitInfo) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into posts (pname,pepname,psalary,pedurequire,plocation,pdetail,pscale,pupdate_time) values(?,?,?,?,?,?,?,?)",
                new Object[]{recuitInfo.getRecuitName(), recuitInfo.getEnterpriseName(), recuitInfo.getSalary(), recuitInfo.getEduRequest(), recuitInfo.getLocation(),recuitInfo.getDetail(),recuitInfo.getScale(),recuitInfo.getUpdateTime()});
    }

    //删除岗位
    public void deletePost(long id){
        SQLiteDatabase db=getWritableDatabase();

        //应先删除投递表中对应该岗位的信息
        db.execSQL("delete from delivers where _dpid=?",new Object[]{id});

        //应先删除收藏表中对应该岗位的信息
        db.execSQL("delete from collects where _copid=?",new Object[]{id});

        db.execSQL("delete from posts where _pid=?",new Object[]{id});

    }

    //更新岗位信息
    public void updatePost(RecuitInfo recuitInfo){
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL("update posts set pname=?,psalary=?,pedurequire=?,plocation=?,pdetail=?,pscale=? ,pupdate_time=? where _pid=?",
                new Object[]{recuitInfo.getRecuitName(),recuitInfo.getSalary(),recuitInfo.getEduRequest(),recuitInfo.getLocation(),recuitInfo.getDetail(),
                        recuitInfo.getScale(),recuitInfo.getUpdateTime(),recuitInfo.getId()});
    }


    
    //根据岗位id得到岗位信息
    public RecuitInfo getRecruitInfoById(long pid){
        SQLiteDatabase db=getReadableDatabase();
        RecuitInfo recuitInfo=null;
        Cursor cursor = db.rawQuery("select _pid,pname,pepname,psalary,pedurequire,plocation,pdetail,pscale,pupdate_time from posts where _pid=?" ,new String[]{String.valueOf(pid)});
        if(cursor.moveToFirst()){
                int index_id=cursor.getColumnIndex("_pid");
                int index_name=cursor.getColumnIndex("pname");
                int index_company=cursor.getColumnIndex("pepname");
                int index_salary=cursor.getColumnIndex("psalary");
                int index_edurequire=cursor.getColumnIndex("pedurequire");
                int index_location=cursor.getColumnIndex("plocation");
                int index_detail=cursor.getColumnIndex("pdetail");
                int index_scale=cursor.getColumnIndex("pscale");
                int index_update_time=cursor.getColumnIndex("pupdate_time");

                long id=cursor.getLong(index_id);
                String name=cursor.getString(index_name);
                String company=cursor.getString(index_company);
                String salary=cursor.getString(index_salary);
                String edurequire=cursor.getString(index_edurequire);
                String location=cursor.getString(index_location);
                String detail=cursor.getString(index_detail);
                String scale=cursor.getString(index_scale);
                String update_time=cursor.getString(index_update_time);
                recuitInfo=new RecuitInfo(id,name,company,salary,edurequire,location,detail,scale,update_time);
            }
        cursor.close();
        return recuitInfo;
    }

    //从岗位信息表获得所有数据
    public List<RecuitInfo> getAllRecruitInfo(){
        List<RecuitInfo> list=new ArrayList<>();
        SQLiteDatabase db=getReadableDatabase();
        Cursor cursor = db.rawQuery("select _pid,pname,pepname,psalary,pedurequire,plocation,pdetail,pscale,pupdate_time from posts" ,null);
        if(cursor.moveToFirst()){
            do {
                int index_id=cursor.getColumnIndex("_pid");
                int index_name=cursor.getColumnIndex("pname");
                int index_company=cursor.getColumnIndex("pepname");
                int index_salary=cursor.getColumnIndex("psalary");
                int index_edurequire=cursor.getColumnIndex("pedurequire");
                int index_location=cursor.getColumnIndex("plocation");
                int index_detail=cursor.getColumnIndex("pdetail");
                int index_scale=cursor.getColumnIndex("pscale");
                int index_update_time=cursor.getColumnIndex("pupdate_time");

                long id=cursor.getLong(index_id);
                String name=cursor.getString(index_name);
                String company=cursor.getString(index_company);
                String salary=cursor.getString(index_salary);
                String edurequire=cursor.getString(index_edurequire);
                String location=cursor.getString(index_location);
                String detail=cursor.getString(index_detail);
                String scale=cursor.getString(index_scale);
                String update_time=cursor.getString(index_update_time);

                RecuitInfo recuitInfo=new RecuitInfo(id,name,company,salary,edurequire,location,detail,scale,update_time);
                list.add(recuitInfo);
            }while(cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    //根据岗位名模糊搜索得到包含某字符串的岗位信息
    public List<RecuitInfo>getRecruitInfoByString(String str){
        List<RecuitInfo> list=new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("select _pid,pname,pepname,psalary,pedurequire,plocation,pdetail,pscale,pupdate_time from posts where pname like ? or pepname like ? or plocation like ?" ,new String[]{"%"+str+"%","%"+str+"%","%"+str+"%"});
        if(cursor.moveToFirst()){
            do {
                int index_id=cursor.getColumnIndex("_pid");
                int index_name=cursor.getColumnIndex("pname");
                int index_company=cursor.getColumnIndex("pepname");
                int index_salary=cursor.getColumnIndex("psalary");
                int index_edurequire=cursor.getColumnIndex("pedurequire");
                int index_location=cursor.getColumnIndex("plocation");
                int index_detail=cursor.getColumnIndex("pdetail");
                int index_scale=cursor.getColumnIndex("pscale");
                int index_update_time=cursor.getColumnIndex("pupdate_time");

                long id=cursor.getLong(index_id);
                String name=cursor.getString(index_name);
                String company=cursor.getString(index_company);
                String salary=cursor.getString(index_salary);
                String edurequire=cursor.getString(index_edurequire);
                String location=cursor.getString(index_location);
                String detail=cursor.getString(index_detail);
                String scale=cursor.getString(index_scale);
                String update_time=cursor.getString(index_update_time);

                RecuitInfo recuitInfo=new RecuitInfo(id,name,company,salary,edurequire,location,detail,scale,update_time);
                list.add(recuitInfo);
            }while(cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    //根据企业的用户名得到该企业下的所有招聘信息；
    public List<RecuitInfo> getOwnRecuitInfoByUsername(String username){
        List<RecuitInfo> list=new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        //先得到该用户的企业名
        Cursor cursor = db.rawQuery("select _pid,pname,pepname,psalary,pedurequire,plocation,pdetail,pscale,pupdate_time from posts" +
                                       " where pepname in (" +
                                                         "select ecompany from enterprises " +
                                                         "where eusername=?)",new String[]{username});
        if(cursor.moveToFirst()){
            do {
                int index_id=cursor.getColumnIndex("_pid");
                int index_name=cursor.getColumnIndex("pname");
                int index_company=cursor.getColumnIndex("pepname");
                int index_salary=cursor.getColumnIndex("psalary");
                int index_edurequire=cursor.getColumnIndex("pedurequire");
                int index_location=cursor.getColumnIndex("plocation");
                int index_detail=cursor.getColumnIndex("pdetail");
                int index_scale=cursor.getColumnIndex("pscale");
                int index_update_time=cursor.getColumnIndex("pupdate_time");

                long id=cursor.getLong(index_id);
                String name=cursor.getString(index_name);
                String company=cursor.getString(index_company);
                String salary=cursor.getString(index_salary);
                String edurequire=cursor.getString(index_edurequire);
                String location=cursor.getString(index_location);
                String detail=cursor.getString(index_detail);
                String scale=cursor.getString(index_scale);
                String update_time=cursor.getString(index_update_time);

                RecuitInfo recuitInfo=new RecuitInfo(id,name,company,salary,edurequire,location,detail,scale,update_time);
                list.add(recuitInfo);
            }while(cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    //得到posts表的所有信息
    public List<List<String>> getAllPosts(){
        SQLiteDatabase db = getReadableDatabase();
        List<List<String>> ans=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from posts ",null);
        if(cursor.moveToFirst()){
            do{
                int index_id=cursor.getColumnIndex("_pid");
                int index_name=cursor.getColumnIndex("pname");
                int index_epname=cursor.getColumnIndex("pepname");
                int index_salary=cursor.getColumnIndex("psalary");
                int index_edurequire=cursor.getColumnIndex("pedurequire");
                int index_location=cursor.getColumnIndex("plocation");
                int index_detail=cursor.getColumnIndex("pdetail");
                int index_scale=cursor.getColumnIndex("pscale");
                int index_update_time=cursor.getColumnIndex("pupdate_time");

                long id=cursor.getLong(index_id);
                String name=cursor.getString(index_name);
                String epname=cursor.getString(index_epname);
                String salary=cursor.getString(index_salary);
                String edurequire=cursor.getString(index_edurequire);
                String location=cursor.getString(index_location);
                String detail=cursor.getString(index_detail);
                String scale=cursor.getString(index_scale);
                String updateTime=cursor.getString(index_update_time);

                List<String> list=new ArrayList<>();
                list.add(String.valueOf(id));
                list.add(name);
                list.add(epname);
                list.add(salary);
                list.add(edurequire);
                list.add(location);
                list.add(detail);
                list.add(scale);
                list.add(updateTime);
                ans.add(list);
            }while (cursor.moveToNext());
        }
        return ans;
    }

    //插入投递信息
    public void insertDeliver(Deliver deliver){
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into delivers (_dcid,_deid,_dpid,dpname,dtime,dstate) values(?,?,?,?,?,?)",
                new Object[]{deliver.getCid(), deliver.getEid(), deliver.getPid(), deliver.getPostName(), deliver.getPostTime(),deliver.getState()});
    }

    //根据用户名id得到投递信息
    public List<Deliver> getDeliversByCId(Long candidate_id){
        List<Deliver> list=new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        //先得到该用户的企业名
        Cursor cursor = db.rawQuery("select _dcid,_deid,_dpid,dpname,dtime,dstate from delivers where _dcid=? ",new String[]{String.valueOf(candidate_id)});
        if(cursor.moveToFirst()){
            do {
                int index_cid=cursor.getColumnIndex("_dcid");
                int index_eid=cursor.getColumnIndex("_deid");
                int index_pid=cursor.getColumnIndex("_dpid");
                int index_pname=cursor.getColumnIndex("dpname");
                int index_time=cursor.getColumnIndex("dtime");
                int index_state=cursor.getColumnIndex("dstate");

                long cid=cursor.getLong(index_cid);
                long eid=cursor.getLong(index_eid);
                long pid=cursor.getLong(index_pid);
                String pname=cursor.getString(index_pname);
                String time=cursor.getString(index_time);
                String state=cursor.getString(index_state);

                Deliver deliver=new Deliver(cid,eid,pid,pname,time,state);
                list.add(deliver);
            }while(cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    //根据企业id得到投递信息
    public List<Deliver> getDeliversByEId(Long enterprise_id){
        List<Deliver> list=new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        //先得到该用户的企业名
        Cursor cursor = db.rawQuery("select _dcid,_deid,_dpid,dpname,dtime,dstate from delivers where _deid=? ",new String[]{String.valueOf(enterprise_id)});
        if(cursor.moveToFirst()){
            do {
                int index_cid=cursor.getColumnIndex("_dcid");
                int index_eid=cursor.getColumnIndex("_deid");
                int index_pid=cursor.getColumnIndex("_dpid");
                int index_pname=cursor.getColumnIndex("dpname");
                int index_time=cursor.getColumnIndex("dtime");
                int index_state=cursor.getColumnIndex("dstate");

                long cid=cursor.getLong(index_cid);
                long eid=cursor.getLong(index_eid);
                long pid=cursor.getLong(index_pid);
                String pname=cursor.getString(index_pname);
                String time=cursor.getString(index_time);
                String state=cursor.getString(index_state);

                Deliver deliver=new Deliver(cid,eid,pid,pname,time,state);
                list.add(deliver);
            }while(cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    //从投递表中根据candidate,enterprise,post的id得到投递的状态
    public String getStateByCidAndEidAndPid(long cid,long eid,long pid){
        SQLiteDatabase db=getReadableDatabase();
        String state="";
        Cursor cursor=db.rawQuery("select dstate from delivers where _dcid=? and _deid=? and _dpid=?",new String[]{String.valueOf(cid),String.valueOf(eid),String.valueOf(pid)});
        if(cursor.moveToFirst()){
            int index_state=cursor.getColumnIndex("dstate");
            state=cursor.getString(index_state);
        }
        return state;
    }

    //根据candidate,enterprise,post的id修改投递表的状态
    public void updateStateById(long cid,long eid,long pid,String state){
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL("update delivers set dstate=? where _dcid=? and _deid=? and _dpid=?",new Object[]{state,cid,eid,pid});
    }

    //取消投递删除该投递信息
    public void deleteDeliverByCidAndEidAndPid(long cid,long eid,long pid){
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL("delete from delivers where _dcid=? and _deid=? and _dpid=?",new Object[]{String.valueOf(cid),String.valueOf(eid),String.valueOf(pid)});
    }

    //在delivers表中根据dpid得到投递该岗位的数量
    public int getDeliveredNumByPid(long pid){
        SQLiteDatabase db=getReadableDatabase();
        Cursor cursor=db.rawQuery("select * from delivers where _dpid=?",new String[]{String.valueOf(pid)});
        int count=cursor.getCount();
        return count;
    }

    //在delivers表中根据dcid得到某人投递该岗位的数量
    public int getDeliveredNumByCid(long cid){
        SQLiteDatabase db=getReadableDatabase();
        Cursor cursor=db.rawQuery("select * from delivers where _dcid=?",new String[]{String.valueOf(cid)});
        int count=cursor.getCount();
        return count;
    }

    //得到delivers表中的所有信息
    public List<List<String>> getAllDelivers(){
        SQLiteDatabase db = getReadableDatabase();
        List<List<String>> ans=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from delivers ",null);
        if(cursor.moveToFirst()){
            do{
                int index_cid=cursor.getColumnIndex("_dcid");
                int index_eid=cursor.getColumnIndex("_deid");
                int index_pid=cursor.getColumnIndex("_dpid");
                int index_pname=cursor.getColumnIndex("dpname");
                int index_time=cursor.getColumnIndex("dtime");
                int index_state=cursor.getColumnIndex("dstate");

                long cid=cursor.getLong(index_cid);
                long eid=cursor.getLong(index_eid);
                long pid=cursor.getLong(index_pid);
                String pname=cursor.getString(index_pname);
                String time=cursor.getString(index_time);
                String state=cursor.getString(index_state);

                List<String> list=new ArrayList<>();
                list.add(String.valueOf(cid));
                list.add(String.valueOf(eid));
                list.add(String.valueOf(pid));
                list.add(pname);
                list.add(time);
                list.add(state);
                ans.add(list);
            }while (cursor.moveToNext());
        }
        return ans;
    }

    //插入面试通知信息
    public void insertInterview(Interview interview) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into interviews (icid,icandidate,ipost,ienterprise,idate,itips,isend_time) values(?,?,?,?,?,?,?)",
                new Object[]{interview.getCid(),interview.getCandidate(), interview.getPost(), interview.getEnterprise(), interview.getInterviewDate(), interview.getInterviewTips(),interview.getSendTime()});
    }

    //根据面试的cid得到面试信息
    public List<Interview> getInterviewByCId(long candidate_id){
        List<Interview> list=new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.rawQuery("select _iid,icid,icandidate,ipost,ienterprise,idate,itips,isend_time from interviews where icid=? ",new String[]{String.valueOf(candidate_id)});
        if(cursor.moveToFirst()){
            do {
                int index_id=cursor.getColumnIndex("_iid");
                int index_cid=cursor.getColumnIndex("icid");
                int index_candidate=cursor.getColumnIndex("icandidate");
                int index_post=cursor.getColumnIndex("ipost");
                int index_enterprise=cursor.getColumnIndex("ienterprise");
                int index_date=cursor.getColumnIndex("idate");
                int index_tips=cursor.getColumnIndex("itips");
                int index_sendtime=cursor.getColumnIndex("isend_time");

                long id=cursor.getLong(index_id);
                long cid=cursor.getLong(index_cid);
                String candidate=cursor.getString(index_candidate);
                String post=cursor.getString(index_post);
                String enterprise=cursor.getString(index_enterprise);
                String date=cursor.getString(index_date);
                String tips=cursor.getString(index_tips);
                String sendtime=cursor.getString(index_sendtime);

                Interview interview=new Interview(id,cid,candidate,post,enterprise,date,tips,sendtime);
                list.add(interview);
            }while(cursor.moveToNext());
        }
        cursor.close();
        return list;
    }

    //根据面试的id得到面试信息
    public Interview getInterviewById(long interview_id){
        SQLiteDatabase db = getReadableDatabase();
        Interview interview=null;

        Cursor cursor = db.rawQuery("select _iid,icid,icandidate,ipost,ienterprise,idate,itips,isend_time from interviews where _iid=? ",new String[]{String.valueOf(interview_id)});
        if(cursor.moveToFirst()){
                int index_id=cursor.getColumnIndex("_iid");
                int index_cid=cursor.getColumnIndex("icid");
                int index_candidate=cursor.getColumnIndex("icandidate");
                int index_post=cursor.getColumnIndex("ipost");
                int index_enterprise=cursor.getColumnIndex("ienterprise");
                int index_date=cursor.getColumnIndex("idate");
                int index_tips=cursor.getColumnIndex("itips");
                int index_sendtime=cursor.getColumnIndex("isend_time");

                long id=cursor.getLong(index_id);
                long cid=cursor.getLong(index_cid);
                String candidate=cursor.getString(index_candidate);
                String post=cursor.getString(index_post);
                String enterprise=cursor.getString(index_enterprise);
                String date=cursor.getString(index_date);
                String tips=cursor.getString(index_tips);
                String sendtime=cursor.getString(index_sendtime);

                interview=new Interview(id,cid,candidate,post,enterprise,date,tips,sendtime);
        }
        cursor.close();
        return interview;
    }

    //得到interviews表中的所有信息
    public List<List<String>> getAllInterviews(){
        SQLiteDatabase db = getReadableDatabase();
        List<List<String>> ans=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from interviews ",null);
        if(cursor.moveToFirst()){
            do{
                int index_id=cursor.getColumnIndex("_iid");
                int index_cid=cursor.getColumnIndex("icid");
                int index_candidate=cursor.getColumnIndex("icandidate");
                int index_post=cursor.getColumnIndex("ipost");
                int index_enterprise=cursor.getColumnIndex("ienterprise");
                int index_date=cursor.getColumnIndex("idate");
                int index_tips=cursor.getColumnIndex("itips");
                int index_sendTime=cursor.getColumnIndex("isend_time");

                long id=cursor.getLong(index_id);
                long cid=cursor.getLong(index_cid);
                String candidate=cursor.getString(index_candidate);
                String post=cursor.getString(index_post);
                String enterprise=cursor.getString(index_enterprise);
                String date=cursor.getString(index_date);
                String tips=cursor.getString(index_tips);
                String sendTime=cursor.getString(index_sendTime);

                List<String> list=new ArrayList<>();
                list.add(String.valueOf(id));
                list.add(String.valueOf(cid));
                list.add(candidate);
                list.add(post);
                list.add(enterprise);
                list.add(date);
                list.add(tips);
                list.add(sendTime);
                ans.add(list);
            }while (cursor.moveToNext());
        }
        return ans;
    }

    //在interviews表中根据icid得到该应聘者的收到面试通知的数量
    public int getInterviewsNumByCid(long cid){
        SQLiteDatabase db=getReadableDatabase();
        Cursor cursor=db.rawQuery("select * from interviews where icid=?",new String[]{String.valueOf(cid)});
        int count=cursor.getCount();
        return count;
    }

    //插入收藏信息
    public void insertCollects(Collection collection) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into collects (_cocid,_copid) values(?,?)",
                new Object[]{collection.getCid(),collection.getPid()});
    }

    //删除收藏信息
    public void deleteCollects(long cid,long pid){
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL("delete from collects where _cocid=? and _copid=? ",new Object[]{String.valueOf(cid),String.valueOf(pid)});
    }

    //判断collects表中是否存在某条收藏信息
    public boolean isExistCollects(long cid,long pid) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from collects where _cocid=? and _copid=?", new String[]{String.valueOf(cid),String.valueOf(pid)});
        boolean flag = cursor.moveToFirst();
        cursor.close();
        return flag;
    }

    //得到collects表的所有信息
    public List<List<String>> getAllCollects(){
        SQLiteDatabase db = getReadableDatabase();
        List<List<String>> ans=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from collects ",null);
        if(cursor.moveToFirst()){
            do{
                int index_cid=cursor.getColumnIndex("_cocid");
                int index_pid=cursor.getColumnIndex("_copid");

                long cid=cursor.getLong(index_cid);
                long pid=cursor.getLong(index_pid);

                List<String> list=new ArrayList<>();
                list.add(String.valueOf(cid));
                list.add(String.valueOf(pid));
                ans.add(list);
            }while (cursor.moveToNext());
        }
        return ans;
    }

    //在collects表中根据_cocid得到该应聘者的收到面试通知的数量
    public int getCollectsNumByCid(long cid){
        SQLiteDatabase db=getReadableDatabase();
        Cursor cursor=db.rawQuery("select * from collects where _cocid=?",new String[]{String.valueOf(cid)});
        int count=cursor.getCount();
        return count;
    }

    //在collects表中根据_copid得到该应聘者的收到面试通知的数量
    public int getCollectsNumByPid(long pid){
        SQLiteDatabase db=getReadableDatabase();
        Cursor cursor=db.rawQuery("select * from collects where _copid=?",new String[]{String.valueOf(pid)});
        int count=cursor.getCount();
        return count;
    }

    //在collects表中根据_cocid得到对应的岗位_copid
    public List<Long> getCollectsPidByCid(long cid){
        List<Long> list=new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor=db.rawQuery("select _copid from collects where _cocid=?",new String[]{String.valueOf(cid)});
        if(cursor.moveToFirst()){
            do{
                int index_pid=cursor.getColumnIndex("_copid");

                long pid=cursor.getLong(index_pid);
                list.add(pid);
            }while (cursor.moveToNext());
        }
        return list;
    }

    //在collects表中根据_copid得到对应的岗位_cocid
    public List<Long> getCollectsCidByPid(long pid){
        List<Long> list=new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor=db.rawQuery("select _cocid from collects where _copid=?",new String[]{String.valueOf(pid)});
        if(cursor.moveToFirst()){
            do{
                int index_cid=cursor.getColumnIndex("_cocid");

                long cid=cursor.getLong(index_cid);
                list.add(cid);
            }while (cursor.moveToNext());
        }
        return list;
    }

    //插入浏览信息
    public void insertBrowse(Browse browse) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into browses (_bcid,_bpid,bcount,blast_time) values(?,?,?,?)",
                new Object[]{browse.getCid(),browse.getPid(),browse.getCount(),browse.getLast_time()});}

    //判断browses表中是否存在某条浏览记录
    public boolean isExistBrowseByCidAndPid(long cid,long pid) {
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from browses where _bcid=? and _bpid=?", new String[]{String.valueOf(cid),String.valueOf(pid)});
        boolean flag = cursor.moveToFirst();
        cursor.close();
        return flag;
    }

    public void updateBrwoseByCidAndPid(long cid,long pid,String currentTime){
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL("update browses set bcount=bcount+1,blast_time=? where _bcid=? and _bpid=?",new String[]{currentTime,String.valueOf(cid),String.valueOf(pid)});
    }

    //得到collects表的所有信息
    public List<List<String>> getAllBrowses(){
        SQLiteDatabase db = getReadableDatabase();
        List<List<String>> ans=new ArrayList<>();
        Cursor cursor=db.rawQuery("select * from browses ",null);
        if(cursor.moveToFirst()){
            do{
                int index_cid=cursor.getColumnIndex("_bcid");
                int index_pid=cursor.getColumnIndex("_bpid");
                int index_count=cursor.getColumnIndex("bcount");
                int index_last_time=cursor.getColumnIndex("blast_time");

                long cid=cursor.getLong(index_cid);
                long pid=cursor.getLong(index_pid);
                long count=cursor.getLong(index_count);
                String lastTime=cursor.getString(index_last_time);

                List<String> list=new ArrayList<>();
                list.add(String.valueOf(cid));
                list.add(String.valueOf(pid));
                list.add(String.valueOf(count));
                list.add(lastTime);
                ans.add(list);
            }while (cursor.moveToNext());
        }
        return ans;
    }

    public String getBrowseTimeByCidAndPid(long cid,long pid){
        String res="";
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor=db.rawQuery("select blast_time from browses where _bcid=? and _bpid=?",new String[]{String.valueOf(cid),String.valueOf(pid)});
        if(cursor.moveToFirst()){
                int index_last_time=cursor.getColumnIndex("blast_time");
                res=cursor.getString(index_last_time);
        }
        return res;
    }

    public long getTotalBrowsesNumByPid(long pid){
        SQLiteDatabase db = getReadableDatabase();
        long totalNum=0;
        Cursor cursor=db.rawQuery("select bcount from browses where _bpid=?",new String[]{String.valueOf(pid)});
        if(cursor.moveToFirst()){
            do{
                int index_count=cursor.getColumnIndex("bcount");
                totalNum+=cursor.getLong(index_count);
            }while (cursor.moveToNext());
        }
        return totalNum;
    }

    //查询某用户收藏的岗位更新后没被浏览的数量
    public long getNoScanRecordsCountByCid(long cid){
        long count=0;
        SQLiteDatabase db=getReadableDatabase();
        Cursor cursor=db.rawQuery("select * from collects,posts,browses where _cocid=? and _copid=_pid and _cocid=_bcid and _copid=_bpid and pupdate_time>blast_time",new String[]{String.valueOf(cid)});
        count=cursor.getCount();
        return count;
    }

}
